﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class Notice
    {
        public ConnectGetData _connectData;
        public Notice()
        {
            _connectData = new ConnectGetData();
        }

        public void NoticeCreate(String classID, String memberID, String content)
        {
            SqlConnection con = _connectData.GetConnect();

            try
            {
                string sql = "insert Notice values (@ClassID, @MemberID, @MessageContent, default)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@ClassID", classID);
                cmd.Parameters.AddWithValue("@MemberID", memberID);
                cmd.Parameters.AddWithValue("@MessageContent", content);
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);

            }
        }

        /// <summary>
        /// get total number of notice of one class
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalNoticeOfOneClass(String classID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "SELECT COUNT(*) FROM Notice WHERE  ClassID in (" + classID+")";
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }

        /// <summary>
        /// get a number of record of a page 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetNoticePaged(int pageIndex, int step, String classID)
        {
            //int startRowIndex = pageIndex * step + 1;
            //int endRowIndex = startRowIndex + step -1;
            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;
            string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY NoticeID DESC) AS number, * FROM NoticeView where ClassID in ("+ classID +") ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";
            
            SqlConnection con = _connectData.GetConnect();
            //      con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);

            //cmd.Parameters.AddWithValue("@TableName", tableName);
            cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
            cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
            DataTable dt = _connectData.GetDataTable(cmd);
            //       con.Close();
            return dt;
        }

        /// <summary>
        /// delete Notice by ID 
        /// </summary>
        /// <param name="NoticeID"></param>
        /// <returns></returns>
        public bool NoticeDelete(String noticeID)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            try
            {
                string sql = "delete from Notice  where NoticeID = @NoticeID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@NoticeID", noticeID);

                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }

        /// <summary>
        /// check if the conversation belong to the current username : teacher
        /// </summary>
        /// <param name="username"></param>
        /// <param name="commentID"></param>
        /// <returns>true if the current user is the owner</returns>
        public bool checkNoticeOwnerForTeacher(String teacherID, String noticeID)
        {
            bool flag = false;

            try
            {
                string sql = "select * from Notice where TeacherID = @TeacherID and NoticeID =@NoticeID ";

                SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                cmd.Parameters.AddWithValue("@TeacherID", teacherID);
                cmd.Parameters.AddWithValue("@NoticeID", noticeID);
                DataTable dt = _connectData.GetDataTable(cmd);

                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Error error error: " + ex.StackTrace);
                return flag;
            }
            return flag;
        }



        internal DataTable GetClassNotice(String classID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sql = "SELECT * FROM NoticeView WHERE  ClassID = @classID";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@classID", classID);

            return _connectData.GetDataTable(cmd);
        }
    }
}